package com.bw.mapper;

import com.bw.domain.Sugar;
import com.bw.domain.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT SUM(amount) value from order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-07 00:00:00'")
    Double getSugarList1();

    @Select("SELECT d.name,sum(i.amount) value from order_info i,order_cargo c,base_dic d WHERE i.id=c.order_id and c.cargo_type=d.id GROUP BY d.name")
    List<Sugar> getSugarList2();

    @Select("SELECT t.start_org_name name,SUM(t.actual_distance) num,count(*) value,TIMESTAMPDIFF(SECOND,t.actual_start_time,t.actual_end_time) time,(TIMESTAMPDIFF(SECOND,t.actual_start_time,t.actual_end_time)/count(*)) vagTime,(SUM(t.actual_distance)/count(*)) avgNum FROM transport_task t,line_base_info b,base_organ o WHERE t.line_id=b.id and b.end_org_id=o.id GROUP BY t.start_org_name\n")
    List<SugarTwo> getSugarList3();

    @Select("SELECT r1.name,count(*) value FROM transport_task_detail d,transport_task t,line_base_info l,base_organ o,base_region_info r,base_region_info r1 WHERE d.transport_task_id=t.id AND t.line_id=l.id and l.org_id=o.id AND o.region_id=r.id and r1.id=r.parent_id GROUP BY r1.name\n")
    List<Sugar> getSugarList4();

    @Select("SELECT d.name,count(*) value FROM order_info i,order_cargo c,base_dic d WHERE i.id=c.order_id and c.cargo_type=d.id GROUP BY d.name\n")
    List<Sugar> getSugarList5();

    @Select("SELECT o.org_name name,count(*) value FROM express_task_delivery d,base_organ o,base_region_info i WHERE d.org_id=o.id and o.region_id=i.id")
    List<Sugar> getSugarList6();

    @Select("SELECT '运输次数' name,count(*) value from order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-07 00:00:00'\n" +
            "UNION\n" +
            "SELECT '运输里程' name,sum(distance) num from order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-07 00:00:00'\n" +
            "UNION\n" +
            "SELECT '运输时长' name,sum(TIMESTAMPDIFF(SECOND,create_time,update_time)) time from order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-07 00:00:00'\n")
    List<Sugar> getSugarList7();

    @Select("SELECT count(*) value from order_info WHERE create_time BETWEEN '2023-01-06 00:00:00' and '2023-01-07 00:00:00'\n")
    Double getSugarList8();
}
